package com.weihe.toolkit.csv;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import com.Ostermiller.util.ExcelCSVParser;
import com.Ostermiller.util.LabeledCSVParser;
import com.weihe.toolkit.file.FileToolkit;
import com.weihe.toolkit.ids.IDGenerator;

public class CsvFileParser {

	private LabeledCSVParser	csvParser;			// csv解析器，对于第一行的表头信息，自动加载为索引关键字

	private int					currLineNum	= -1;	// 文件所读到行数

	private String[]			currLine	= null;	// 用来存放当前行的数据

	/*
	 * 构造函数， Param: in InputStream 要解析的信息流 throws IOException
	 */

	protected CsvFileParser(InputStream in) throws IOException {

		csvParser = new LabeledCSVParser(new ExcelCSVParser(in));
		currLineNum = csvParser.getLastLineNumber();
	}

	/*
	 * 检查是否还有数据
	 * 
	 * return ture 还有一行数据，false 没有数据
	 */
	public boolean hasMore() throws IOException {
		currLine = csvParser.getLine();
		currLineNum = csvParser.getLastLineNumber();
		if (null == currLine)
			return false;
		return true;
	}

	/*
	 * 返回当前行数据，关键字所指向的数据 param:String filedName 该行的表头 return:String
	 * 返回当前行数据，关键字所指向的数据
	 */
	public String getByFieldName(String fieldName) {

		return csvParser.getValueByLabel(fieldName);
	}

	/*
	 * 关闭解析器
	 */
	public void close() throws IOException {
		csvParser.close();

	}

	/*
	 * 读取当前行数据
	 * 
	 * return String[] 读取当前行数据
	 */
	public String[] readLine() throws IOException {
		currLine = csvParser.getLine();

		currLineNum = csvParser.getLastLineNumber();

		return currLine;

	}

	public int getCurrLineNum() {

		return currLineNum;

	}

	public static void main(String[] args) throws Exception {

		// 创建解析信息流
		InputStream in = new FileInputStream(new File(
				"d:\\Social Login.csv"));

		// 实例解析器CsvFileParser
		CsvFileParser parser = new CsvFileParser(in);

		int i = 0;
		int jcount = 100000/30;
		long cc = 0L;
		// 读取数据
		while (parser.hasMore()) {
			i++;
			String s = parser.getByFieldName("age_range").trim();
			int agemax = 1;
			int agemin = 1;
			if(s.indexOf("-")>=0){
				String[] array = s.split("-");
				if(array.length==2){
					agemin = Integer.parseInt(array[0]);
					agemax = Integer.parseInt(array[1]);
				}
			}else if(s.indexOf("+")>=0){
				s = s.replace("+", "");
				agemin = Integer.parseInt(s);
			}
			
			String bir = parser.getByFieldName("birthday").trim();
			SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yy");
			Long birthday = 0L;
			try {
				Date inputDate = sdf.parse(bir);
				birthday = inputDate.getTime();
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
			bir = parser.getByFieldName("created_at").trim();
			sdf = new SimpleDateFormat("MM/dd/yy");
			if(bir.indexOf("/") >= 4){
				sdf = new SimpleDateFormat("yyyy/MM/dd");
			}
			Long created_at = 0L;
			try {
				Date inputDate = sdf.parse(bir);
				created_at = inputDate.getTime();
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
			bir = parser.getByFieldName("gender").trim();
			int gen = 2;
			if(StringUtils.equalsIgnoreCase("f", bir)){
				gen = 1;
			}else if(StringUtils.equalsIgnoreCase("m", bir)){
				gen = 0;
			}
			
			String iden = IDGenerator.newUUIDOf32();
			
			bir = parser.getByFieldName("last_visit").trim();
			sdf = new SimpleDateFormat("MM/dd/yy");
			if(bir.indexOf("/") >= 4){
				sdf = new SimpleDateFormat("yyyy/MM/dd");
			}
			Long last_visit = 0L;
//			try {
//				Date inputDate = sdf.parse(bir);
//				last_visit = inputDate.getTime();
//			} catch (ParseException e) {
//				e.printStackTrace();
//			}
			cc = (i/jcount)*3600L*24*1000;
			last_visit = System.currentTimeMillis() - cc;
			System.out.println(last_visit);
			sdf = new SimpleDateFormat("yyyy/MM/dd");
			System.out.println(sdf.format(new Date(last_visit)));
			
			bir = parser.getByFieldName("sns_vendor").trim();
			//1:facebook 2:twitter 3:linkedin 4:google
			int sns = 4;
			if(StringUtils.equalsIgnoreCase("fb", bir)){
				sns = 1;
			}else if(StringUtils.equalsIgnoreCase("TWTR", bir)){
				sns = 2;
			}else if(StringUtils.equalsIgnoreCase("LNKD", bir)){
				sns = 3;
			}
			
			String sql = "INSERT INTO ga_social_guest(age_range_max, age_range_min, birthday, created_at, customer_id,"+
            "email, favourites_count, followers_count, friends_count, gender, "+
            "identity, language, last_visit, listed_count, location, "+
            " name, phone_number, sns_vendor, time_zone, visit_freq)"+
    "VALUES ("+ agemax +","+agemin+","+birthday+","+created_at+",'0000001444',"+
            "'"+parser.getByFieldName("email").trim()+"',"+parser.getByFieldName("favourites").trim()+","+parser.getByFieldName("followers").trim()+","+
            parser.getByFieldName("friends").trim()+","+gen+",'"+iden+"','"+parser.getByFieldName("language").trim()+"',"+last_visit+","+parser.getByFieldName("listed_count").trim()+",'"+parser.getByFieldName("location").trim()+"','"+parser.getByFieldName("name").trim()+"','"+
            parser.getByFieldName("phone_number").trim()+"',"+sns+",'"+parser.getByFieldName("time_zone").trim()+"',"+parser.getByFieldName("visit_freq")+");";
			
//			System.out.println(sql);
			FileToolkit.appendToFile("d:\\social-1.sql", sql+"\r\n");

		}

		parser.close();
		System.out.println(i);
	}

}
